﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Common;
using Automobile.service.Model;
using System.Data.SqlClient;
using System.Data;
namespace Automobile.service.DAL
{
    public class SupplierDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <returns></returns>
        public DataTable ShowSupplier()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from Supplier");
            return helper.ExecDataTable(builder.ToString());
        }
        /// <summary>
        /// 根据供应商编号删除供应商信息()
        /// </summary>
        /// <returns></returns>
        public int DelSupplier(string id)
        {
            SqlParameter[] par = { 
                                new SqlParameter("@str",id)
                                };

            return helper.ExecNonquery_Proc("Proc_Delsupplier", par);
        }
        
        /// <summary>
        /// 添加供货商信息
        /// </summary>
        /// <returns></returns>
        public int AddSupplier(Supplier s)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into Supplier values(@Supplier_Name,@Supplier_People,@Supplier_Phone,@Supplier_Addr)");
            SqlParameter[] par = { 
                                new SqlParameter("@Supplier_Name",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_People",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_Phone",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_Addr",SqlDbType.VarChar,40)
                                };
            par[0].Value = s.Supplier_Name;
            par[1].Value = s.Supplier_People;
            par[2].Value = s.Supplier_Phone;
            par[3].Value = s.Supplier_Addr;
            return helper.ExecNonquery(builder.ToString(), par);
        }
        
        /// <summary>
        /// 根据名字获取供应商信息
        /// </summary>
        /// <returns></returns>
        public DataTable SearchSupplier(string Supplier_Name)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from Supplier where Supplier_Name=@id");
            SqlParameter[] par = { 
                                new SqlParameter("@id",SqlDbType.VarChar,40)
                                };
            par[0].Value = Supplier_Name;
            return helper.ExecDataTable(builder.ToString(), par);
        }
        /// <summary>
        /// 根据编号获取供货商信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSupplierbyID(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from Supplier where Supplier_Id=@id");
            SqlParameter[] par = { 
                                new SqlParameter("@id",SqlDbType.VarChar,40)
                                };
            par[0].Value = id;
            return helper.ExecDataTable(builder.ToString(), par);
        }
        /// <summary>
        /// 获取全部信息记录数
        /// </summary>
        /// <returns></returns>
        public int GetCountSupplier()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(*) from Supplier");
            return Convert.ToInt32(helper.ExecScalar(builder.ToString()));
        }
        /// <summary>
        /// 修改供货商信息
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int uptSupplier(Supplier s)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update Supplier set Supplier_Name=@Supplier_Name,Supplier_People=@Supplier_People,Supplier_Phone=@Supplier_Phone,Supplier_Addr=@Supplier_Addr where Supplier_Id=@Supplier_Id");
            SqlParameter[] par = { 
                                new SqlParameter("@Supplier_Id",SqlDbType.Int,4),
                                new SqlParameter("@Supplier_Name",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_People",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_Phone",SqlDbType.VarChar,40),
                                new SqlParameter("@Supplier_Addr",SqlDbType.VarChar,40)
                                };
            par[0].Value = s.Supplier_Id;
            par[1].Value = s.Supplier_Name;
            par[2].Value = s.Supplier_People;
            par[3].Value = s.Supplier_Phone;
            par[4].Value = s.Supplier_Addr;
            return helper.ExecNonquery(builder.ToString(), par);
        }
        
    }
}
